package com.ejoysoft.wlanyz.business;

import java.util.Vector;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;
import com.ejoysoft.common.DbConnect;
import com.ejoysoft.common.Globa;

public class SignalClassOneAc {
	private Globa globa;
    private DbConnect db;


    public SignalClassOneAc() {
    }

    public SignalClassOneAc(Globa globa) {
        this.globa = globa;
        db = globa.db;
    }

    public SignalClassOneAc(Globa globa, boolean b) {
        this.globa = globa;
        db = globa.db;
        if (b) globa.setDynamicProperty(this);
    }

    String strTableName1= "ac_class_associate";
    String strTableName2 = "rm_device";
    String strTableName4 = "nhm_channel_change";
    
   
    

    //记录集转化为对象
    public SignalClassOneAc load(ResultSet rs, boolean isView,String strDegree,String strStartTime,String strEndTime) {
    	
        SignalClassOneAc theBean = new SignalClassOneAc();
        DbConnect dbConnect = new DbConnect();
        
        try {
        	 String strTableName3;
   		  if(strDegree.equals("小时"))
        		 strTableName3 = "nhm_ap_rssi_kpi_hour";
        	 else strTableName3 = "nhm_ap_rssi_kpi_day";
        	 
            //theBean.setstrId(rs.getString(1));
        	theBean.setstrCity(rs.getString(1));//地市
            theBean.setstrCountry(rs.getString(2));//区县
            theBean.setclassOneAC(rs.getString(3));//一级热点
            theBean.setacNumber(rs.getInt(4));//二级热点个数
            theBean.setapNumber(rs.getInt(5));//AP个数
            String sql = " select device_id from (select * from "+strTableName1+" where strcity = '"+theBean.getstrCity()+"' and strcountry = '"+theBean.getstrCountry()+"' and classoneac = '"+theBean.getclassOneAC()+"') a  left join "+strTableName2+" on hotspot = classtwoac ";
            
            String sql1 = "select avg(rsn),avg(rssi) from ("+sql+") t1,"+strTableName3+" t2 where 1=1 ";
            String sql2 = " and t1.device_id = t2.device_id  ";
            
            if(strStartTime.length()>0&&strEndTime.length()>0)
     		 {
     			 strStartTime="to_date('" + strStartTime + "','dd-MM-yy hh24.mi.ss') ";
     			 strEndTime="to_date('" + strEndTime + "','dd-MM-yy hh24.mi.ss') ";
     			 sql1 = sql1.concat(" and t2.timestamp>=" + strStartTime).concat(" and t2.timestamp<=" + strEndTime).concat(sql2);
     		 }
             
     		 else if(strStartTime.length()>0&&!(strEndTime.length()>0))
     		 {
     			 strStartTime="to_date('" + strStartTime + "','dd-MM-yy hh24.mi.ss') ";
             	sql1 = sql1.concat(" and t2.timestamp>=" + strStartTime).concat(sql2);
     		 }
     		 
             else if(!(strStartTime.length()>0)&&strEndTime.length()>0)
             {
             	strEndTime="to_date('" + strEndTime + "','dd-MM-yy hh24.mi.ss') ";
             	sql1 = sql1.concat(" and t2.timestamp<=" + strEndTime).concat(sql2);
             }
             else sql1 =sql1.concat(sql2);
           //System.out.println("***************8"+sql1);
           ResultSet rs1 = dbConnect.executeQuery(sql1);
           if(rs1.next())
           { 
           double rsn = rs1.getDouble(1);
           BigDecimal b = new BigDecimal(rsn);
           rsn = b.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();//保留两位小数
           theBean.setnoiseRatio(rsn);
         	double rssi = rs1.getDouble(2);
         	BigDecimal c = new BigDecimal(rssi);
         	rssi = c.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();//保留两位小数
         	theBean.setfieldIntens(rssi);//场强
           }
           rs1.close();
         	 sql1 = "select avg(quality) from ("+sql+") t1,"+strTableName4+" t2 where 1=1 ";
             sql2 = " and t1.device_id = t2.device_id  ";
             if(strStartTime.length()>0&&strEndTime.length()>0)
     		 {
     			 sql1 = sql1.concat(" and t2.timestamp>=" + strStartTime).concat(" and t2.timestamp<=" + strEndTime).concat(sql2);
     		 }
             
     		 else if(strStartTime.length()>0&&!(strEndTime.length()>0))
     		 {
     			 	sql1 = sql1.concat(" and t2.timestamp>=" + strStartTime).concat(sql2);
     		 }
     		 
             else if(!(strStartTime.length()>0)&&strEndTime.length()>0)
             {
             	sql1 = sql1.concat(" and t2.timestamp<=" + strEndTime).concat(sql2);
             }
             else sql1 =sql1.concat(sql2);
             ResultSet rs2 = dbConnect.executeQuery(sql1);
            // System.out.println("***************8"+sql1);
             if(rs2.next())
             {
         	 double quality = rs2.getDouble(1);
          	 BigDecimal d = new BigDecimal(quality);
          	 quality = d.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();//保留两位小数
          	theBean.setrouteQuality(quality);//信道质量
             }
           rs2.close();
           } 
        catch (Exception e) {
        	   
            e.printStackTrace();
            //System.out.println("00000"+e);
        }
        dbConnect.closeCon();
        return theBean;
    }
   
    //获取某个城市区县的一级热点的AP的场强
    public ArrayList<Double> getAPNRsn(String strCity,String strCountry,String strClassOneAC,String strDegree,String strStartTime,String strEndTime)
    {
    	ArrayList<Double>  beans = new ArrayList<Double>();
    	//double rsn;
    	String strTableName3;
 		  if(strDegree.equals("小时"))
      		 strTableName3 = "nhm_ap_rssi_kpi_hour";
      	 else strTableName3 = "nhm_ap_rssi_kpi_day";
 		  try{
    	String sql1 = " select device_id from  (select * from "+strTableName1+" where strcity = '"+strCity+"' and strcountry = '"+strCountry+"'and classoneac = '"+strClassOneAC+"') a left join "+strTableName2+" b on hotspot=classtwoac ";
    	String sql = " select t1.device_id,avg(rssi)as rssi  from ("+sql1+") t1,"+strTableName3+" t2 where 1=1 ";
    	String sql2=" and t1.device_id = t2.device_id  group by t1.device_id ";
    	
    	if(strStartTime.length()>0&&strEndTime.length()>0)
		 {
			 strStartTime="to_date('" + strStartTime + "','dd-MM-yy hh24.mi.ss') ";
			 strEndTime="to_date('" + strEndTime + "','dd-MM-yy hh24.mi.ss') ";
			 sql = sql.concat(" and t2.timestamp>=" + strStartTime ).concat(" and t2.timestamp<=" + strEndTime);
		 }
        
		 else if(strStartTime.length()>0&&!(strEndTime.length()>0))
		 {
			 strStartTime="to_date('" + strStartTime + "','dd-MM-yy hh24.mi.ss') ";
        	sql = sql.concat(" and t2.timestamp>=" + strStartTime );
		 }
		 
        else if(!(strStartTime.length()>0)&&strEndTime.length()>0)
        {
        	strEndTime="to_date('" + strEndTime + "','dd-MM-yy hh24.mi.ss') ";
        	sql = sql.concat(" and t2.timestamp<=" + strEndTime);
        }
    	sql=sql.concat(sql2);
    	//System.out.println("***************8"+sql);
        ResultSet rs1 = db.executeQuery(sql);
        if(rs1 !=null&&rs1.next())
        { 
        	do{
        	beans.add(rs1.getDouble(2));
        	}while(rs1.next());
        }
        rs1.close();
        
 		}
        catch (Exception e) {
     	   
            e.printStackTrace();
        }
    	return beans;
    
    }
    //获取某个城市区县一级热点的AP的场强变化
    public ArrayList<Object[]> getAPNRsnChange(String strCity,String strCountry,String classOneAc,String strDegree,String strStartTime,String strEndTime)
    {
    	ArrayList<Object[]>  beans = new ArrayList<Object[]>();
    	//SimpleDateFormat df=new SimpleDateFormat("yyyy-mm-dd");

    	//double rsn;
    	String strTableName3;
 		  if(strDegree.equals("小时"))
      		 strTableName3 = "nhm_ap_rssi_kpi_hour";
      	 else strTableName3 = "nhm_ap_rssi_kpi_day";
 		  try{
    	String sql1 = " select device_id from  (select * from "+strTableName1+" where strcity = '"+strCity+"' and strcountry = '"+strCountry+"' and classoneac = '"+classOneAc+"' ) a left join "+strTableName2+" b on hotspot=classtwoac ";
    	String sql = " select t2.timestamp,avg(rssi)as rssi  from ("+sql1+") t1,"+strTableName3+" t2 where 1=1 ";
    	String sql2=" and t1.device_id = t2.device_id  group by t2.timestamp order by t2.timestamp ";
    	
    	if(strStartTime.length()>0&&strEndTime.length()>0)
		 {
			 strStartTime="to_date('" + strStartTime + "','dd-MM-yy hh24.mi.ss') ";
			 strEndTime="to_date('" + strEndTime + "','dd-MM-yy hh24.mi.ss') ";
			 sql = sql.concat(" and t2.timestamp>=" + strStartTime ).concat(" and t2.timestamp<=" + strEndTime);
		 }
        
		 else if(strStartTime.length()>0&&!(strEndTime.length()>0))
		 {
			 strStartTime="to_date('" + strStartTime + "','dd-MM-yy hh24.mi.ss') ";
        	sql = sql.concat(" and t2.timestamp>=" + strStartTime );
		 }
		 
        else if(!(strStartTime.length()>0)&&strEndTime.length()>0)
        {
        	strEndTime="to_date('" + strEndTime + "','dd-MM-yy hh24.mi.ss') ";
        	sql = sql.concat(" and t2.timestamp<=" + strEndTime);
        }
    	sql=sql.concat(sql2);
    	//System.out.println("***************8"+sql);
        ResultSet rs1 = db.executeQuery(sql);
        if(rs1 !=null&&rs1.next())
        { 
        	do{
        		Object[] theBean = new Object[2];
				String city = rs1.getString(1);
				double rssi = rs1.getDouble(2);
				theBean[0] = city;
				theBean[1] = rssi;
				beans.add(theBean);
        	}while(rs1.next());
        }
        rs1.close();
        
 		}
        catch (Exception e) {
     	   
            e.printStackTrace();
        }
    	return beans;
    
    }
    
    
    //调出城市所有区县名称
    public ArrayList<String> getAllstrcountryByName( String city)
    {
    	ArrayList<String>  beans = new ArrayList<String>();
    	String sql = "select distinct(strcountry) from "+strTableName1+" ";
    	String where = " where strCity='"+city+"'";
    	sql = sql+where;
    	ResultSet re = db.executeQuery(sql);
    	try { 
			if(re != null && re.next())
			{   //String strcountry = re.getString("");
				do{
					String strcountry = re.getString("strcountry");
					beans.add(strcountry);
				}while(re.next());
			}
			re.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return beans;
    }
    //调出城市区县所有一级热点名称
    public ArrayList<String> getAllclassOneAcByName( String city,String country)
    {
    	ArrayList<String>  beans = new ArrayList<String>();
    	String sql = "select distinct(classoneac) from "+strTableName1+" ";
    	String where = " where strCity='"+city+"' and strcountry = '"+country+"'";
    	sql = sql+where;
    	System.out.println("999999"+sql);
    	ResultSet re = db.executeQuery(sql);
    	try { 
			if(re != null && re.next())
			{   //String strcountry = re.getString("");
				do{
					String strcountry = re.getString("classoneac");
					beans.add(strcountry);
				}while(re.next());
			}
			 re.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return beans;
    }
    //
 
  //查询符合条件的记录总数
    public int getCount( String where) {
         
    	int count = 0;
    	
         try {
        	
        	 String sql = "select  count(distinct classoneac) from "+strTableName1+" ";
        	 if (where.length() > 0) {
    			 where = where.toLowerCase();
    			 if (where.indexOf("order") > 0)
    				 where = where.substring(0, where.lastIndexOf("order"));
    			 sql = String.valueOf(sql) + String.valueOf(where);
    			 }
        	// System.out.println("222222222222222222222"+sql);
        	 
    		 ResultSet rs = db.executeQuery(sql);
    		 if (rs.next())
    			 count = rs.getInt(1);
    		 rs.close();
    		 return count;
    	 }
         catch (Exception ee) {
        	 ee.printStackTrace();
        	 
             return count;
             }
         }
//列表记录集
    public Vector<SignalClassOneAc> list(String strDegree,String where,int startRow, int rowCount,String strStartTime,String strEndTime) {
    	
    	Vector<SignalClassOneAc> beans = new Vector<SignalClassOneAc>();
     	 
    	 try {
    		 
    		  String sql = "select strcity,strcountry, classoneac,count(distinct classtwoac),count(distinct device_id) from "+strTableName1+" a left join "+strTableName2+" b on hotspot=classtwoac ";
     		 
     		if (where.length() > 0)
  			  sql = String.valueOf(sql) + String.valueOf(where);
  		  
     		 sql += "  group by strcity,strcountry,classoneac";
    		  
    		  Statement s = db.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);//链接数据库
    		  
    		  if (startRow != 0 && rowCount != 0)
    			  s.setMaxRows((startRow + rowCount) - 1);
    		//  System.out.println("3333333333333333333333"+sql);
    		  
    		  ResultSet rs = s.executeQuery(sql);
    		  
    		  if (rs != null && rs.next()) {
    			  if (startRow != 0 && rowCount != 0)
    				  rs.absolute(startRow);
    			  do {
    				  SignalClassOneAc theBean = new SignalClassOneAc();
    				  theBean = load(rs, false,strDegree,strStartTime,strEndTime);
    				  beans.addElement(theBean);
    				  } while (rs.next());
    			  }
    		  
    		  rs.close();
    		  s.close();
    		  
    	 }
    	 
    	 catch (Exception ee) {
    		 ee.printStackTrace();
    		 }
    	 return beans;
     }
	public String returnCityName(String fontString)
	{
	return null;
	}
	

	private String strCity;//		地市
    private String strCountry;//		县区
    private String classOneAC;  //一级热点
    private int classTwoAcNum;//	         二级 热点数量
    private int    apNumber;//		AP数量
    private int    userNumber;//	用户数
    private double fieldIntens;//	平均场强
    private double noiseRatio ;//	信噪比
    private double routeQuality;//  信道质量
   
 
    public String getstrCity() {
        return strCity;
    }

    public void setstrCity(String strCity) {
        this.strCity = strCity;
    }
 
    public String getstrCountry() {
        return strCountry;
    }

    public void setstrCountry(String strCountry) {
        this.strCountry = strCountry;
    }
    
    public String getclassOneAC() {
        return classOneAC;
    }

    public void setclassOneAC(String classOneAC) {
        this.classOneAC = classOneAC;
    }
    public int getacNumber() {
		return classTwoAcNum;
	}
    
    public void setacNumber(int classTwoAcNum){
    	 this.classTwoAcNum = classTwoAcNum;
    }
    
    public int getapNumber() {
		return apNumber;
	}
    
    public void setapNumber(int apNumber){
    	this.apNumber = apNumber;
    }
    
    public int getuserNumber(){
    	return userNumber;
    }
    
    public void setuserNumber(int userNumber){
    	this.userNumber=userNumber;
    }
    
    public Number getnoiseRatio () {
        return noiseRatio ;
    }

    public void setnoiseRatio (double noiseRatio ) {
    	BigDecimal b = new BigDecimal(noiseRatio);
    	noiseRatio = b.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();//保留两位小数
        this.noiseRatio  = noiseRatio ;
        
    }
    

    public double getfieldIntens () {
        return fieldIntens;
    }

    public void setfieldIntens(double fieldIntens) {
    	BigDecimal b = new BigDecimal(fieldIntens);
    	fieldIntens = b.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();//保留两位小数
      
        this.fieldIntens = fieldIntens;
    }
    public double getfrouteQuality() {
        return routeQuality;
    }

    public void setrouteQuality(double routeQuality) {
    	BigDecimal b = new BigDecimal(routeQuality);
    	routeQuality = b.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();//保留两位小数
      
        this.routeQuality = routeQuality;
    }
}
 
